﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace TestApp
{
    class CsvClass
    {

        public List<string> GetCsvHeaderAndValue(string CustomerRef)
        {
            string _Header = string.Empty;
            string _Body = string.Empty;

            try
            {
                // Header
                DataTable dt = GetDataTable(CustomerRef);
                if (dt == null)
                    return null;

                string[] colLbls = new string[dt.Columns.Count];

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    colLbls[i] = dt.Columns[i].ColumnName;
                    colLbls[i] = GetWriteableValueForCsv(colLbls[i]);
                }

                // Write labels
                _Header = string.Join(",", colLbls);

                // Rows of Data
                foreach (DataRow rowData in dt.Rows)
                {
                    string[] colData = new string[dt.Columns.Count];

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        object obj = rowData[i];
                        colData[i] = GetWriteableValueForCsv(obj);
                    }

                    // Write data in row
                    _Body = string.Join(",", colData);
                }

                List<string> ret = new List<string>();
                ret.Add(_Header);
                ret.Add(_Body);
                return ret;

            }
            catch (Exception)
            {

                return null;
            }
 
        }

        private DataTable GetDataTable(string CustomerRef)
        {
            SqlDataAdapter dataAdapter = null;

            //connection
            string connectionstring = @"Data Source=192.168.203.220;Initial Catalog=Westpac_BusinessToolbox;User ID=sa;Password=masterkey"; ;

            //sp
            string sqlstatement = string.Format("exec GetStatByRefNo {0}", CustomerRef);

            try
            {
                dataAdapter = new SqlDataAdapter(sqlstatement, connectionstring);

                // Populate a new data table and bind it to the BindingSource.
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(table);
                return table;
            }
            catch (Exception)
            {

            }
            finally
            {

                if (dataAdapter != null)
                {
                    dataAdapter.Dispose();
                }


            }

            return null;
        }

        private string GetWriteableValueForCsv(object obj)
        {
            try
            {
                // Nullable types to blank
                if (obj == null || obj == Convert.DBNull)
                    return "";

                // if string has no ','
                if (obj.ToString().IndexOf(",") == -1)
                    return obj.ToString();

                // remove backslahes
                return "\"" + obj.ToString() + "\"";
            }
            catch (Exception)
            {

                return "";
            }

        }

    }
}
